import datetime
import shutil
from pathlib import Path

import openpyxl
from openpyxl.utils import get_column_letter
from openpyxl.utils import get_column_letter
import tools_pmc




def 建立留存文件夹():
    lj_source_file = "//192.168.70.101/19计划管理部/01.计划/5，物控管理/2，呆滞料管理/长库龄线缆处理/data/" + tools_pmc.today_str1
    folderPath = Path(lj_source_file)
    folderPath.mkdir(parents=True, exist_ok=True)
    # 主文件夹路径
    shutil.copy("//192.168.70.101/19计划管理部/01.计划/5，物控管理/2，呆滞料管理/长库龄线缆处理/长库龄线缆处理台账.xlsx",
                "//192.168.70.101/19计划管理部/01.计划/5，物控管理/2，呆滞料管理/长库龄线缆处理/data/" + tools_pmc.today_str1 + "/长库龄线缆处理台账.xlsx")


def dict_colf():
    dict1 = {}
    for ii in range(1, ws_mc.max_column + 1):
        dict1[ws_mc.cell(1, ii).value] = ii
    return dict1


def 规格预计消耗周期(i, 品名: str, 规格: str):
    规格四项合计 = 0
    规格365天消耗量 = 0
    for ii in range(2, ws_mc.max_row + 1):
        if ws_mc.cell(ii, dict_col['规格']).value == 规格 and ws_mc.cell(ii, dict_col['品名']).value == 品名:
            规格四项合计 += (ws_mc.cell(ii, dict_col['可用数量']).value +
                             ws_mc.cell(ii, dict_col['在途数量']).value +
                             ws_mc.cell(ii, dict_col['在检数量']).value +
                             ws_mc.cell(ii, dict_col['已请未购数量']).value)
            规格365天消耗量 += ws_mc.cell(ii, dict_col['365天消耗量']).value
    if 规格四项合计 > 0:
        if 规格365天消耗量 == 0:
            return 999
        else:
            return int(规格四项合计 / 规格365天消耗量 * 365)
    else:
        return 0


def 线缆参数分解(规格: str, offset: int):
    规格参数 = {}
    线缆长度 = 0
    接头类型 = {}
    if 规格[0:4] == "IKDC" and 规格.split("-")[2] == "CL":
        if float(规格.split("-")[1]) > int(float(规格.split("-")[1])):
            线缆长度 = int(float(规格.split("-")[1])) + offset
        else:
            线缆长度 = int(float(规格.split("-")[1]))
        接头类型 = {规格.split("-")[3][0], 规格.split("-")[3][1:]}
        temp = set(规格.split("-")[4:])
        temp.add("CL")
        规格参数 = temp
    elif 规格[0:4] == "IKDC" and 规格.split("-")[2] == "CX":
        if float(规格.split("-")[1]) > int(float(规格.split("-")[1])):
            线缆长度 = int(float(规格.split("-")[1])) + offset
        else:
            线缆长度 = int(float(规格.split("-")[1]))
        接头类型 = {规格.split("-")[3][0], 规格.split("-")[3][1:]}
        temp = set(规格.split("-")[4:])
        temp.add("CX")
        规格参数 = temp
    elif 规格[0:4] == "IKDC" and 规格.split("-")[2] == "CX12":
        if float(规格.split("-")[1]) > int(float(规格.split("-")[1])):
            线缆长度 = int(float(规格.split("-")[1])) + offset
        else:
            线缆长度 = int(float(规格.split("-")[1]))
        接头类型 = {规格.split("-")[3][0], 规格.split("-")[3][1:]}
        temp = set(规格.split("-")[4:])
        temp.add("CX12")
        规格参数 = temp
    elif 规格[0:4] == "IKDC" and "GE" in 规格.split("-")[2]:
        线缆长度 = int(规格.split("-")[1])
        规格参数 = set(规格.split("-")[2:])
    elif 规格[0:4] == "IKPD":
        线缆长度 = int(规格.split("-")[3][-2:])
        temp = set(规格.split("-")[1:3])
        temp.update(set(规格.split("-")[4:]))
        if 规格.split("-")[3][0] == "S":
            temp.add("S")
        else:
            temp.add("N")
        规格参数 = temp
    elif 规格[0:4] == "IKIO" and 规格.split("-")[1] == "F3" and 规格.split("-")[2] == "254":
        线缆长度 = int(规格.split("-")[3])
        temp = set(规格.split("-")[1:3])
        temp.update(set(规格.split("-")[4:]))
        规格参数 = temp
    return [线缆长度, 接头类型, 规格参数]


def 线缆改制消耗准入判定表(wb: openpyxl.workbook.workbook.Workbook):
    ws = wb.worksheets[0]
    list1 = ["品号", "品名", "规格", "规格预计消耗周期>180天", "总备货量预计消耗周期>180天",
             "在库超180天>0",
             "最后请购日期距今>100天", "综合判定"]
    list2 = [14, 26, 28, 12, 12, 12, 12, 12]
    for ii in range(len(list1)):
        ws.cell(2, ii + 1).value = list1[ii]
        ws.cell(2, ii + 1).fill = tools_pmc.fill_blue
        ws.cell(2, ii + 1).alignment = tools_pmc.alignment_center
        ws.column_dimensions[get_column_letter(ii + 1)].width = list2[ii]
    # noinspection PyTypeChecker
    ws.row_dimensions[2].height = 27
    ws.freeze_panes = 'A3'
    ws.auto_filter.ref = "A2:" + get_column_letter(ws.max_column) + str(ws.max_row)
    ws.cell(1, 1).value = "线缆改制消耗准入判定表"
    ws.cell(1, 3).value = "报表更新日期：" + tools_pmc.today_str1

    list_pass = ["103090010137",  # 1.5米+M头（原材料线缆）
                 "103090020163"]  # IKPD-1205-H06-S1.2-F-2
    dict_可改制线缆 = {}
    dict_线缆参数 = {}
    a = 2
    for i in range(2, ws_mc.max_row + 1):
        if ws_mc.cell(i, dict_col['类别']).value == '线缆' and ws_mc.cell(i, dict_col['品号']).value not in list_pass:
            # dict_线缆参数[ws_mc.cell(i, dict_col['品号']).value] = 线缆参数分解(ws_mc.cell(i, dict_col['规格']).value,
            #                                                                     1)
            a += 1
            ws.cell(a, 1).value = ws_mc.cell(i, dict_col['品号']).value
            ws.cell(a, 2).value = ws_mc.cell(i, dict_col['品名']).value
            ws.cell(a, 3).value = ws_mc.cell(i, dict_col['规格']).value
            ws.cell(a, 4).value = 规格预计消耗周期(i, ws_mc.cell(i, dict_col['品名']).value,
                                                   ws_mc.cell(i, dict_col['规格']).value)
            ws.cell(a, 5).value = ws_mc.cell(i, dict_col['总备货量预计消耗周期']).value
            ws.cell(a, 6).value = ws_mc.cell(i, dict_col['在库超180天']).value
            if ws_mc.cell(i, dict_col['最后请购日期']).value is not None:
                ws.cell(a, 7).value = ws_mc.cell(i, dict_col['最后请购日期']).value.date()
                ws.cell(i, 7).number_format = 'yyyy/mm/dd'
            bool1 = True
            if 规格预计消耗周期(i, ws_mc.cell(i, dict_col['品名']).value, ws_mc.cell(i, dict_col['规格']).value) > 180:
                ws.cell(a, 4).fill = tools_pmc.fill_green
            else:
                bool1 = False
                ws.cell(a, 4).fill = tools_pmc.fill_red
            if ws_mc.cell(i, dict_col['总备货量预计消耗周期']).value > 180:
                ws.cell(a, 5).fill = tools_pmc.fill_green
            else:
                bool1 = False
                ws.cell(a, 5).fill = tools_pmc.fill_red
            if ws_mc.cell(i, dict_col['在库超180天']).value > 0:
                ws.cell(a, 6).fill = tools_pmc.fill_green
            else:
                bool1 = False
                ws.cell(a, 6).fill = tools_pmc.fill_red
            if ws_mc.cell(i, dict_col['最后请购日期']).value is None:
                ws.cell(a, 7).fill = tools_pmc.fill_green
            else:
                if (datetime.date.today() - ws_mc.cell(i, dict_col['最后请购日期']).value.date()).days > 100:
                    ws.cell(a, 7).fill = tools_pmc.fill_green
                else:
                    ws.cell(a, 7).fill = tools_pmc.fill_red
                    bool1 = False
            if bool1:
                ws.cell(a, 8).value = "√"
                dict_可改制线缆[ws_mc.cell(i, dict_col['规格']).value] = ws_mc.cell(i, dict_col['品号']).value
    return dict_可改制线缆, dict_线缆参数


建立留存文件夹()
wb_mc = openpyxl.load_workbook(tools_pmc.lj_mc)
ws_mc = wb_mc.worksheets[0]
dict_col = dict_colf()

wb = openpyxl.Workbook()
dict_可改制线缆, dict_线缆参数 = 线缆改制消耗准入判定表(wb)
# while True:
#     input1 = input("请输入需求线缆品号：")


wb.save(
    "//192.168.70.101/19计划管理部/01.计划/5，物控管理/2，呆滞料管理/长库龄线缆处理/data/" + tools_pmc.today_str1 + "/" + tools_pmc.today_str1 + "线缆改制消耗准入判定表.xlsx")
